#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time : 2021/10/27 下午2:20
# @Author : The Kite Runner
# @File : dict_db.py
# @Software: PyCharm


import pymysql
import hashlib


class Dict:
    # 链接mysql
    def __init__(self):
        self.kwargs = {
            'host': 'localhost',
            'port': 3306,
            'user': 'root',
            'password': '123456',
            'database': 'dict',
            'charset': 'utf8'
        }

        self.db = pymysql.connect(**self.kwargs)
        self.cur = self.db.cursor()

    # 加密方法
    def hash_encrytion(self, passwd):
        hash = hashlib.sha256()  # 生成加密对象
        hash.update(passwd.encode())  # 加密
        return hash.hexdigest()  # 返回加密后的字串

    def close(self):
        self.cur.close()
        self.db.close()

    # 注册函数
    def do_register(self, name, passwd):
        """

        @param name:用户名,查看数据库中是否可以插入
        @param passwd: 对用户密码进行加密存储
        @return: 返回是否添加成功:T/F
        """
        passwd = self.hash_encrytion(passwd)
        sql = "insert into user (user,passwd) values(%s,%s);"
        try:
            self.cur.execute(sql, [name, passwd])
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    # 登录函数
    def login(self, user, passwd):
        """
        @param user:用户名:查看该用户名是否存在在用户表中
        @param passwd: 密码:进行加密比对是否为该用户本人
        @return: 返回T/F
        """
        passwd = self.hash_encrytion(passwd)
        sql = 'select id from user where user=%s and passwd=%s;'
        try:
            self.cur.execute(sql, [user, passwd])
            if self.cur.fetchone():
                return True
        except Exception as e:
            print(e)

    # 查单词
    def query(self, word):
        """
        查询单词得到元祖,如果元祖为空代表单词不存在返回NONE
        单词存在返回单词解释:元祖第一个元素('indef art one',)
        @param word:查询单词
        @return:
        """
        sql = 'select mean from words where word=%s;'
        self.cur.execute(sql, [word])
        result = self.cur.fetchone()
        if result:
            return result[0]

    # 查看历史纪录
    def do_history(self, user, word):
        """
        根据用户名和单词,查询到单词id,和用户id,存储到每个用户历史纪录表中
        @param user:用户名
        @param word: 单词
        @return:
        """
        # id user_id words_id time
        sql = 'select id from user where user=%s;'
        self.cur.execute(sql, [user])
        user_id = self.cur.fetchone()
        sql = 'select id from words where word=%s'
        self.cur.execute(sql, [word])
        words_id = self.cur.fetchone()
        sql = 'insert into hists (user_id,words_id) values (%s,%s);'
        self.cur.execute(sql, [user_id, words_id])
        self.db.commit()

    # 历史纪录查询
    def hist(self, user):
        """
        运用链接查询:用户名,单词,时间
        @param user:用户名
        @return: 返回前十条查询结果
        """
        # user word time
        sql = "select user,word,time from user inner join hists on user.id=hists.user_id inner join words on words.id=hists.words_id where user=%s order by time desc limit 10;"
        self.cur.execute(sql, [user])
        return self.cur.fetchall()
